//  File:		DataSetDest.cs
//  Summary:	Implementation of a managed DataSet destination component. This component sample demonstrates
//				writing a managed destination component. It adds the rows it receives in ProcessInput to a datatable
//				and if directed to do so by the SaveDataSetToXml custom property, saves the resulting data set to 
//				an xml file after execution.
//	
//
//  Date:		6/15/2004
//
//---------------------------------------------------------------------
//
//  This file is part of the Microsoft SQL Server Code Samples.
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//	This source code is intended only as a supplement to Microsoft
//	Development Tools and/or on-line documentation.  See these other
//	materials for detailed information regarding Microsoft code samples.
//
//	THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF //ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO //THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//	PARTICULAR PURPOSE.
//
//===================================================================== 
namespace Microsoft.Samples.SqlServer.Dts
{
    using System;
    using System.IO;
    using System.Data;
    using System.Collections;
    using System.Xml;
    using System.Xml.Schema;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.SqlServer.Dts.Pipeline;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

    #region DtsPipelineComponentAttribute

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1001:TypesThatOwnDisposableFieldsShouldBeDisposable"), DtsPipelineComponent
    (DisplayName = "DataSet DestinationCS",
    Description = "DataSet Destination",
    IconResource = "Microsoft.Samples.SqlServer.Dts.DataSetDest.ico",
    ComponentType = ComponentType.DestinationAdapter)]

    #endregion

    public class DataSetDestination : PipelineComponent
    {
        #region Members

        private DataSet dataSet;
        private DataTable runtimeDataTable;
        private string xmlDestinationFile;
        private bool cancel;
        ColumnInfo[] columnInfos;

        struct ColumnInfo
        {
            public int bufferIndex;
            public string Name;
        }

        #endregion

        #region Design Time

        #region ProvideComponentProperties
        /// <summary>
        /// </summary>
        public override void ProvideComponentProperties()
        {
            // Reset the component.
            base.RemoveAllInputsOutputsAndCustomProperties();
            ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

            // RunTimeVariable custom property 
            // Contains the name of an existing runtime variable which the dataset is assigned  after execution.
            // If it does not contain a valid variable, then fail validation.
            IDTSCustomProperty100 dataSetVariable = ComponentMetaData.CustomPropertyCollection.New();
            dataSetVariable.Name = "RuntimeVariable";
            dataSetVariable.Description = "Name of user defined variable where the System.Data.DataSet object is placed after execution.";

            IDTSCustomProperty100 existingDataTableName = ComponentMetaData.CustomPropertyCollection.New();
            existingDataTableName.Name = "DataTableName";
            existingDataTableName.Value = "";
            existingDataTableName.Description = "Specifies the name given to the DataSet DataTable; or if the DataTable exists, the name of the DataTable to append to.";

            IDTSCustomProperty100 saveDsToDisk = ComponentMetaData.CustomPropertyCollection.New();
            saveDsToDisk.Name = "SaveDataSetToXml";
            saveDsToDisk.Value = false;
            saveDsToDisk.Description = "Specifies whether the DataSet is saved to disk at the end of execution.";

            //	Add a single input.
            // Set the IsUsed property to true, so the ExternalMetadata collection is shown in the 
            // advanced properties editor.
            IDTSInput100 input = ComponentMetaData.InputCollection.New();
            input.Name = "DataSetDestinationInput";
            input.HasSideEffects = true;
        }
        #endregion

        #region AcquireConnections
        /// <summary>
        /// Called at design time and runtime. Establishes a connection using a ConnectionManager in the package.
        /// </summary>
        /// <param name="transaction">Not used.</param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId = "System.Exception.#ctor(System.String)")]
        public override void AcquireConnections(object transaction)
        {
            if (ComponentMetaData.RuntimeConnectionCollection.Count == 1)
            {
                IDTSRuntimeConnection100 connection = ComponentMetaData.RuntimeConnectionCollection[0];
                if (connection.ConnectionManager != null)
                {
                    ConnectionManager cm = DtsConvert.GetWrapper(connection.ConnectionManager);

                    this.xmlDestinationFile = cm.AcquireConnection(null) as string;

                    //	If AcquireConnection does not return a string, then
                    // the ConnectionManager is not a FILE connectionmanager.
                    if (this.xmlDestinationFile == null)
                    {
                        throw new Exception("The ConnectionManager " + cm.Name + " is not a FILE connection manager.");
                    }
                }
            }
        }
        #endregion

        #region ReleaseConnections
        /// <summary>
        /// Releases the connection established in AcquireConnections
        /// </summary>
        public override void ReleaseConnections()
        {
            this.xmlDestinationFile = "";
        }
        #endregion

        #region ReinitializeMetaData
        /// <summary>
        /// </summary>
        public override void ReinitializeMetaData()
        {
            ComponentMetaData.RemoveInvalidInputColumns();
        }
        #endregion

        #region Validate
        /// <summary>
        /// Validate the component based on the following criteria:
        ///		1. Base class validation passes.
        ///		2. It has no outputs.
        ///		3. It has only one input.
        ///		4. If specified, the RuntimeVariable property is assigned a valid runtime variable.
        ///		5. There are no referenced upstream columns that no longer exist.
        ///		6. There are no columns that are ReadWrite or ignored.
        /// </summary>
        /// <returns>
        /// A status from the DTSValidationStatus enum indicating the result of Validation.
        /// </returns>
        [CLSCompliant(false)]
        public override DTSValidationStatus Validate()
        {
            // Make sure there are no outputs.
            if (ComponentMetaData.OutputCollection.Count != 0)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "Should not have any ouput objects.", "", 0, out this.cancel);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            // Make sure there is one input.
            if (ComponentMetaData.InputCollection.Count != 1)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "Should only have a single input.", "", 0, out this.cancel);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            // Verify that if a runtime variable is specified, that it points to an existing variable, and that the 
            // Type of the variable is object.
            IDTSCustomProperty100 runtimeVariable = this.ComponentMetaData.CustomPropertyCollection["RuntimeVariable"];
            if (runtimeVariable.Value != null)
            {
                // Verify the specified variable exists.
                if (VariableDispenser.Contains(runtimeVariable.Value.ToString()) == false)
                {
                    ComponentMetaData.FireError(HResults.DTS_E_VARIABLENOTFOUND, ComponentMetaData.Name, ComponentMetaData.GetErrorDescription(HResults.DTS_E_VARIABLENOTFOUND), "", 0, out this.cancel);
                    return DTSValidationStatus.VS_ISBROKEN;
                }

                // Verify the object type of the variable.
                IDTSVariables100 variables = null;
                this.VariableDispenser.LockOneForRead((string)runtimeVariable.Value, ref variables);
                if (DtsConvert.TypeCodeFromVarType((ushort)variables[0].DataType) != TypeCode.Object)
                {
                    ComponentMetaData.FireError(0, ComponentMetaData.Name, "The variable " + (string)runtimeVariable.Value + " specified for the " + runtimeVariable.Name + " custom property is not Type object.", "", 0, out this.cancel);
                    return DTSValidationStatus.VS_ISBROKEN;
                }
            }

            if (ComponentMetaData.AreInputColumnsValid == false)
            {
                return DTSValidationStatus.VS_NEEDSNEWMETADATA;
            }

            if ((bool)ComponentMetaData.CustomPropertyCollection["SaveDataSetToXml"].Value == true && ComponentMetaData.RuntimeConnectionCollection.Count > 0)
            {
                if (ComponentMetaData.RuntimeConnectionCollection["XmlFile"].ConnectionManager == null)
                {
                    ComponentMetaData.FireError(0, ComponentMetaData.Name, "The SaveDataSetToXml property is true, but a connection manager has not been provided.", "", 0, out this.cancel);
                    return DTSValidationStatus.VS_ISBROKEN;
                }
            }

            return base.Validate();
        }
        #endregion

        #region SetUsageType
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId = "System.Exception.#ctor(System.String)"), CLSCompliant(false)]
        public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
        {
            // Disallow read write columns.
            if (usageType == DTSUsageType.UT_READWRITE)
            {
                throw new Exception("Columns must be UT_READONLY");
            }

            return base.SetUsageType(inputID, virtualInput, lineageID, usageType);
        }
        #endregion

        #region OnInputPathAttached
        /// <summary>
        /// Called when the component is attached to an upstream component. Automatically map all of the columns
        /// from the virtual input by calling SetUsageType.
        /// </summary>
        /// <param name="inputID">The ID of the input that is attached.</param>
        public override void OnInputPathAttached(int inputID)
        {
            IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);

            IDTSVirtualInput100 vInput = input.GetVirtualInput();

            foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
            {
                this.SetUsageType(inputID, vInput, vCol.LineageID, DTSUsageType.UT_READONLY);
            }
        }

        #endregion

        #region SetComponentProperty
        [CLSCompliant(false)]
        public override IDTSCustomProperty100 SetComponentProperty(string propertyName, object propertyValue)
        {
            // If the SaveDataSetToXml is set to true, then add a runtime connection.
            if (propertyName == "SaveDataSetToXml" && (bool)propertyValue)
            {
                // Add a RuntimeConnection object.
                IDTSRuntimeConnection100 xmlFileConnection = ComponentMetaData.RuntimeConnectionCollection.New();
                xmlFileConnection.Name = "XmlFile";
            }
            else if (propertyName == "SaveDataSetToXml" && !(bool)propertyValue)
            {
                // If the property is set to false, then remove the runtime connection
                // if it exists.
                if (ComponentMetaData.RuntimeConnectionCollection.Count > 0)
                {
                    ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
                }
            }

            return base.SetComponentProperty(propertyName, propertyValue);
        }
        #endregion

        #endregion

        #region Runtime

        #region PreExecute
        /// <summary>
        /// Set up the dataset 
        /// For each IDTSInput100, create a DataTable in the DataSet
        /// For each IDTSInputColumn100, create a DataColumn in the DataTable.
        /// During ProcessInput these DataTables and DataColumns are populated from the Buffer provided by 
        /// the DataFlow engine.
        /// </summary>
        public override void PreExecute()
        {
            this.CreateDataSet();

            IDTSInput100 input = ComponentMetaData.InputCollection[0];
            this.columnInfos = new ColumnInfo[input.InputColumnCollection.Count];

            for (int col = 0; col < input.InputColumnCollection.Count; col++)
            {
                ColumnInfo colInfo = new ColumnInfo();

                colInfo.bufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[col].LineageID);
                colInfo.Name = input.InputColumnCollection[col].Name;
                this.columnInfos[col] = colInfo;
            }
        }
        #endregion

        #region ProcessInput
        /// <summary>
        /// Add the rows from the input buffer to the to the DataSet.
        /// </summary>
        /// <param name="inputID">The ID of the IDTSInput100</param>
        /// <param name="buffer">The PipelineBuffer containing the records to process</param>
        public override void ProcessInput(int inputID, PipelineBuffer buffer)
        {
            if (buffer == null)
            {
                throw new ArgumentNullException("buffer");
            }

            if (!buffer.EndOfRowset)
            {
                while (buffer.NextRow() == true)
                {
                    // IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
                    DataRow row = this.runtimeDataTable.NewRow();

                    for (int x = 0; x < this.columnInfos.Length; x++)
                    {
                        ColumnInfo ci = (ColumnInfo)this.columnInfos[x];

                        if (!buffer.IsNull(ci.bufferIndex))
                        {
                            BufferColumn bc = buffer.GetColumnInfo(ci.bufferIndex);
                            if (bc.DataType == DataType.DT_BYTES)
                            {
                                row[ci.Name] = buffer.GetBytes(ci.bufferIndex);
                            }
                            else if (bc.DataType == DataType.DT_IMAGE)
                            {
                                byte[] bytes = new byte[buffer.GetBlobLength(ci.bufferIndex)];
                                bytes = buffer.GetBlobData(ci.bufferIndex, 0, bytes.Length);
                                row[ci.Name] = bytes;
                            }
                            else
                            {
                                row[ci.Name] = buffer[ci.bufferIndex];
                            }
                        }
                    }

                    // Add the row to the DataTable
                    this.runtimeDataTable.Rows.Add(row);
                }
            }
        }
        #endregion

        #region PostExecute
        /// <summary>
        /// Called at the end of execution. Copies the dataset to the runtime variable and if specified, saves the 
        /// dataset as an xml document.
        /// </summary>
        public override void PostExecute()
        {
            //	Assign the dataset to the runtime variable
            IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection["RuntimeVariable"];
            IDTSVariables100 vars = null;

            if (prop.Value != null && VariableDispenser.Contains((string)prop.Value))
            {
                VariableDispenser.LockOneForRead((string)prop.Value, ref vars);
                vars[0].Value = this.dataSet;
            }

            if ((bool)ComponentMetaData.CustomPropertyCollection["SaveDataSetToXml"].Value == true)
            {
                this.dataSet.WriteXml(this.xmlDestinationFile, XmlWriteMode.WriteSchema);
            }
        }
        #endregion

        #endregion

        #region Helpers

        private void CreateDataSet()
        {
            // Create the dataset. 
            this.dataSet = new DataSet(ComponentMetaData.Name);

            this.dataSet.Locale = System.Globalization.CultureInfo.InvariantCulture;

            // IDTSInput100 input = ComponentMetaData.InputCollection[0];

            bool fileExists = File.Exists(this.xmlDestinationFile);

            // Does the file specified by the connection manager exist?
            if (fileExists)
            {
                // Yes, load the data, and read the schema. 
                this.dataSet.ReadXml(this.xmlDestinationFile, XmlReadMode.ReadSchema);
            }

            this.runtimeDataTable = this.GetDataTable(this.dataSet);
        }

        private DataTable GetDataTable(DataSet fromDataSet)
        {
            // NOTE: When the destination table already exists,
            // check to make sure that the data type and other properties
            // of its columns match those of the input columns.
            // Otherwise, an exception will occur during processing.

            DataTable dt = null;

            IDTSCustomProperty100 existingDataTable = ComponentMetaData.CustomPropertyCollection["DataTableName"];

            // Is a name provided for the data table?
            if (existingDataTable.Value != null
                && ((string)existingDataTable.Value).Length > 0)
            {
                // Yes, does the provided table name exist in the DataSet?
                if (fromDataSet.Tables.Contains(existingDataTable.Value.ToString()))
                {
                    // Yes, use it.
                    dt = fromDataSet.Tables[existingDataTable.Value.ToString()];
                }
                else
                {
                    // No the table doesn't exist, create one with the provided name.
                    dt = fromDataSet.Tables.Add(existingDataTable.Value.ToString());
                }
            }
            else
            {
                // Check to see if the dataset has a table with the same name as the input.
                if (fromDataSet.Tables.Contains(ComponentMetaData.InputCollection[0].ID.ToString(System.Globalization.CultureInfo.InvariantCulture)))
                {
                    dt = fromDataSet.Tables[ComponentMetaData.InputCollection[0].ID.ToString(System.Globalization.CultureInfo.InvariantCulture)];
                }
                else
                {
                    // A name for the DataTable was not provided, so default to the ID of the input.
                    dt = fromDataSet.Tables.Add(ComponentMetaData.InputCollection[0].ID.ToString(System.Globalization.CultureInfo.InvariantCulture));
                }
            }

            if (dt.Columns.Count == 0)
            {
                IDTSInput100 input = ComponentMetaData.InputCollection[0];

                // And create the columns based on the input columns.
                for (int cols = 0; cols < input.InputColumnCollection.Count; cols++)
                {
                    IDTSInputColumn100 col = input.InputColumnCollection[cols];
                    DataColumn dc = dt.Columns.Add(col.Name);

                    bool isLong = false;
                    DataType dataType = ConvertBufferDataTypeToFitManaged(col.DataType, ref isLong);
                    dc.DataType = BufferTypeToDataRecordType(dataType);
                }
            }
            return dt;
        }
        #endregion
    }
}